﻿//using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using Warensoft.DataService.Models;
using Warensoft.Unity.Communication.Client.DbClient;
namespace Warensoft.DataService
{
    public  class InstantiableDBHelper
    {
        

       

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        
        /// <summary>
        /// 获取数据连接的用户名
        /// </summary>
        public  string DBUserName
        {
            get
            {
                
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(this.ConnectionString );
                return builder.UserID;
                
            }
        }

        public DbProviderFactory CurrentDbProviderFactory
        {
            get
            {
                return System.Data.SqlClient.SqlClientFactory.Instance;
            }
        }
      
        public InstantiableDBHelper(string connectionString)
        {
            this.ConnectionString = connectionString;
        }
        /// <summary>
        /// 返回一个数据集
        /// </summary>
        public  ResultInfo CreateDataSet(Query query)
        {
            return CreateDataSet(query.CommandText, query.Parameters);
        }
        /// <summary>
        /// 返回一个数据集
        /// </summary>
        public  ResultInfo CreateDataSet(string commandText, params WDbParameter[] parameters)
        {
            ResultInfo result = new ResultInfo ();
            using (var con = CreateConnection())
            {
                try
                {
                    var cmd = con.CreateCommand();
                    cmd.CommandText = commandText;
                    if (parameters != null && parameters.Length != 0)
                    {
                        cmd.Parameters.AddRange(parameters.Select(p => this.CreateParameter(p.ParameterName,p.Value)).ToArray ());
                        
                    }
                    DbDataAdapter adpt = CurrentDbProviderFactory.CreateDataAdapter();
                    adpt.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);
                    result.OperationDone = true;
                    result.AdditionalData = ds;
                }
                catch (Exception ex)
                {
                    result.OperationDone = false;
                    result.Message = ex.Message;
                   
                }
            }
            return result;
        }

        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        public  DbConnection CreateConnection()
        {
            var con = new SqlConnection();
            con.ConnectionString = ConnectionString;

            return con;
        }
        public  DbConnection CreateConnection(string connectionString)
        {
            var con = new SqlConnection();
            con.ConnectionString = connectionString;

            return con;
        }
        /// <summary>
        /// 创建查询参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public  DbParameter CreateParameter(string name, object value)
        {
            var param = this.CurrentDbProviderFactory.CreateParameter();
            param.ParameterName = name;
            param.Value = value;
            return param;
        }
        /// <summary>
        /// 执行只返回一个值的SQL语句
        /// </summary>
        public  ResultInfo ExecuteScalar(Query query)
        {
            return ExecuteScalar(query.CommandText, query.Parameters);
        }
        /// <summary>
        /// 执行只返回一个值的SQL语句
        /// </summary>
        public  ResultInfo ExecuteScalar(string commandText, params WDbParameter[] parameters)
        {
            ResultInfo result = new ResultInfo();
            using (var con = CreateConnection())
            {
                using (var cmd = con.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = commandText;
                        if (parameters != null && parameters.Length != 0)
                        {
                            cmd.Parameters.AddRange(parameters.Select (p=>this.CreateParameter (p.ParameterName ,p.Value)).ToArray());
                        }
                        con.Open();
                        result.AdditionalData =cmd.ExecuteScalar();
                        result.OperationDone = true;
                    }
                    catch (Exception ex)
                    {
                        result.OperationDone = false;
                        result.Message = ex.Message;
                        
                    }

                }
            }
            return result;
        }
       
        /// <summary>
        /// 执行非选择性操作
        /// </summary>
        public  ResultInfo ExecuteNonQuery(Query query)
        {
            return ExecuteNonQuery(query.CommandText, query.Parameters);
        }
        /// <summary>
        /// 执行非选择性操作
        /// </summary>
        public  ResultInfo ExecuteNonQuery(string commandText, params WDbParameter[] parameters)
        {
            ResultInfo result = new ResultInfo();
            using (var con = CreateConnection())
            {

                var cmd = con.CreateCommand();
                cmd.CommandText = commandText;
                if (parameters != null && parameters.Length != 0)
                {
                    cmd.Parameters.AddRange(parameters.Select(p => this.CreateParameter(p.ParameterName, p.Value)).ToArray());
                }
                con.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                    result.OperationDone = true;

                }
                catch (Exception ex)
                {
                    cmd.Dispose();
                    result.OperationDone = false;
                    result.Message = ex.Message;
                    
                }

            }
            return result;
        }

        /// <summary>
        /// 返回一个具备数据表结构的空数据集
        /// </summary>
        public  ResultInfo CreateDataSetSchema(string tableName)
        {
            ResultInfo result = new ResultInfo ();
            using (var con = CreateConnection())
            {

                var cmd = con.CreateCommand();
                cmd.CommandText = "select * from " + tableName;
                var adpt = this.CurrentDbProviderFactory.CreateDataAdapter();
                adpt.SelectCommand = cmd ;
                DataSet ds = new DataSet();
                try
                {
                    adpt.FillSchema(ds, SchemaType.Mapped);
                    result.AdditionalData = ds;
                    result.OperationDone = true;
                }
                catch (Exception ex)
                {
                    cmd.Dispose();
                    adpt.Dispose();
                    ds.Clear();
                    ds.Dispose();
                    result.OperationDone = false;
                    result.Message = ex.Message;
                    
                }
            }
            return result;
        }
        /// <summary>
        /// 更新数据集，包括添加、修改、删除
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="tableName">对应的表名</param>
        /// <returns></returns>
        public ResultInfo UpdateDataSet(DataSet ds, string tableName)
        {
            ResultInfo result = new ResultInfo();
            using (var con=this.CreateConnection ())
            {
                try
                {
                    var cmd = con.CreateCommand();
                    cmd.CommandText = "select * from " + tableName;
                    var adpt = this.CurrentDbProviderFactory.CreateDataAdapter();
                    adpt.SelectCommand = cmd;
                    var builder = this.CurrentDbProviderFactory.CreateCommandBuilder();
                    builder.DataAdapter = adpt;
                    builder.ConflictOption = ConflictOption.OverwriteChanges;
                    adpt.InsertCommand = builder.GetInsertCommand();
                    adpt.UpdateCommand = builder.GetUpdateCommand();
                    adpt.DeleteCommand = builder.GetDeleteCommand();
                    adpt.Update(ds);
                    result.OperationDone = true;
                }
                catch (Exception ex)
                {
                    result.OperationDone = false;
                    result.Message = ex.Message;

                }
            }
            return result;
        }
        

        public DbDataAdapter CreateDataAdapter(DbCommand selectCommand)
        {
            var adpt = this.CurrentDbProviderFactory.CreateDataAdapter();
            adpt.SelectCommand = selectCommand;
            return adpt;
        }

        public DbCommandBuilder CreateCommandBuilder(DbDataAdapter adpt)
        {
            var builder = this.CurrentDbProviderFactory.CreateCommandBuilder();
            builder.DataAdapter = adpt;
            return builder;
        }
    }
}
